Extract Data from JSON File
Step 1: Now go to SSIS Toolbox and drag and drop "Data Flow Task" to Control Flow and rename it.
*Reminder: If you don’t see the SSIS Toolbox, go to View à Other Windows à SSIS Toolbox or Right click on an empty space inside the Control Flow or Data Flow and select SSIS Toolbox.
Step 2: Double click on Data Flow Task then drag
and drop Script Component to Data Flow.
We’ll be asked what type of Script Component we want to add. Choose Source and click the OK button.
Step 3: Double-click on the Script Component, go to the Inputs and Outputs tab. Expand the Output 0 node, click on the Output Columns and add 40 columns by clicking on the Add Column button
**Reminder: All the data types for each column keep them as String with the length of 255. We will change these data types later in the steps.
Step 5: Go to the Script tab
and click the Edit Script… button.
Go to Solution Explorer à References à Right click à Add Reference… and select System.Web.Extensions and System.Windows.Forms library.
Step 6: Right click on the
solution name in Solution Explorer à Add à Class and name the class GunViolence. Copy the below code to put in GunViolence.cs. Click 
to
save.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace GunViolenceNameSpace
{
class GunViolence
{
public string Date { get; set; }
public string School { get; set; }
public string City { get; set; }
public string State { get; set; }
public string KilledShooter { get; set; }
public string WoundedVictims { get; set; }
public string KilledVictims { get; set; }
public string GenderOfVictims { get; set; }
public string VictimAffiliationWithSchool { get; set; }
public string VictimAge { get; set; }
public string VictimRace { get; set; }
public string VictimEthnicity { get; set; }
public string TargetedSpecificVictim { get; set; }
public string RandomVictims { get; set; }
public string Bullied { get; set; }
public string DomesticViolence { get; set; }
public string SuicideByShooter { get; set; }
public string ShooterActions { get; set; }
public string PrePlannedSchoolAttack { get; set; }
public string Category { get; set; }
public string SchoolType { get; set; }
public string TimeOfOccurrence { get; set; }
public string DurationInMinutes { get; set; }
public string DayOfWeek { get; set; }
public string IsSchoolDay { get; set; }
public string TimePeriod { get; set; }
public string DuringSportingEvent { get; set; }
public string DuringSchoolSponsoredEvent { get; set; }
public string Location { get; set; }
public string NumberOfShotsFired { get; set; }
public string FirearmType { get; set; }
public string NumberOfShooters { get; set; }
public string ShooterName { get; set; }
public string ShooterAge { get; set; }
public string ShooterGender { get; set; }
public string ShooterRace { get; set; }
public string ShooterEthnicity { get; set; }
public string ShooterAffiliationWithSchool { get; set; }
public string ShooterAccomplianceFired { get; set; }
public string HostagesTaken { get; set; }
}
}
to save.String jsonFileContent = File.ReadAllText(@"D:\ERUM\ETLProject1\SourcesEtlProject1\csvjson.json");
JavaScriptSerializer js = new JavaScriptSerializer() { MaxJsonLength = 86753090 };
List<GunViolence> ViolenceListV = js.Deserialize<List<GunViolence>>(jsonFileContent);
foreach (GunViolence GunViolenceList in ViolenceListV)
{
Output0Buffer.AddRow();
Output0Buffer.Date = GunViolenceList.Date;
Output0Buffer.School = GunViolenceList.School;
Output0Buffer.City = GunViolenceList.City;
Output0Buffer.Date = GunViolenceList.Date;
Output0Buffer.State = GunViolenceList.State;
Output0Buffer.KilledShooter = GunViolenceList.KilledShooter;
Output0Buffer.WoundedVictims = GunViolenceList.WoundedVictims;
Output0Buffer.KilledVictims = GunViolenceList.KilledVictims;
Output0Buffer.GenderOfVictims = GunViolenceList.GenderOfVictims;
Output0Buffer.VictimAffiliationWithSchool = GunViolenceList.VictimAffiliationWithSchool;
Output0Buffer.VictimAge = GunViolenceList.VictimAge;
Output0Buffer.VictimRace = GunViolenceList.VictimRace;
Output0Buffer.VictimEthnicity = GunViolenceList.VictimEthnicity;
Output0Buffer.TargetedSpecificVictim = GunViolenceList.TargetedSpecificVictim;
Output0Buffer.RandomVictims = GunViolenceList.RandomVictims;
Output0Buffer.Bullied = GunViolenceList.Bullied;
Output0Buffer.DomesticViolence = GunViolenceList.DomesticViolence;
Output0Buffer.SuicideByShooter = GunViolenceList.SuicideByShooter;
Output0Buffer.ShooterActions = GunViolenceList.ShooterActions;
Output0Buffer.PrePlannedSchoolAttack = GunViolenceList.PrePlannedSchoolAttack;
Output0Buffer.Category = GunViolenceList.Category;
Output0Buffer.SchoolType = GunViolenceList.SchoolType;
Output0Buffer.TimeOfOccurrence = GunViolenceList.TimeOfOccurrence;
Output0Buffer.DurationInMinutes = GunViolenceList.DurationInMinutes;
Output0Buffer.DayOfWeek = GunViolenceList.DayOfWeek;
Output0Buffer.IsSchoolDay = GunViolenceList.IsSchoolDay;
Output0Buffer.TimePeriod = GunViolenceList.TimePeriod;
Output0Buffer.DuringSportingEvent = GunViolenceList.DuringSportingEvent;
Output0Buffer.DuringSchoolSponsoredEvent = GunViolenceList.DuringSchoolSponsoredEvent;
Output0Buffer.Location = GunViolenceList.Location;
Output0Buffer.NumberOfShotsFired = GunViolenceList.NumberOfShotsFired;
Output0Buffer.FirearmType = GunViolenceList.FirearmType;
Output0Buffer.NumberOfShooters = GunViolenceList.NumberOfShooters;
Output0Buffer.ShooterName = GunViolenceList.ShooterName;
Output0Buffer.ShooterAge = GunViolenceList.ShooterAge;
Output0Buffer.ShooterGender = GunViolenceList.ShooterGender;
Output0Buffer.ShooterRace = GunViolenceList.ShooterRace;
Output0Buffer.ShooterEthnicity = GunViolenceList.ShooterEthnicity;
Output0Buffer.ShooterAffiliationWithSchool = GunViolenceList.ShooterAffiliationWithSchool;
Output0Buffer.ShooterAccomplianceFired = GunViolenceList.ShooterAccomplianceFired;
Output0Buffer.HostagesTaken = GunViolenceList.HostagesTaken;
}
}
}
What is JSON?
JSON stands for JavaScript Object Notation
JSON is a lightweight format for storing and transporting data
JSON is often used when data is sent from a server to a web page
JSON is "self-describing" and easy to understand
Click the link to read more about JSON